﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;

namespace DAO
{
    public class BCThang_DAO
    {
        //ham kiem tra
        public static bool kiemTraBCThang(int nam, int thang)
        {
            string query = "select * from lichbay where year(ngaydi) = " + nam.ToString() + " and month(ngaydi) = " + thang.ToString();
            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);
            if (dt.Rows.Count == 0)
                return false;
            return true;
        }

        public static List<BCThang_DTO> lapBaoCaoThang(int nam, int thang)
        {
            string query = "SELECT CB.MACB MACB, SUM(LB2.SOVEHANG1 + LB2.SOVEHANG2) SOVE, SUM(LB2.SOVEHANG1*CB.GIAVEHANG1 + LB2.SOVEHANG2*CB.GIAVEHANG2) DOANHTHU "
                            + " FROM CHUYENBAY CB JOIN (SELECT LB.MALB MALB, LB.MACB MACB, "
                            + " SUM (CASE WHEN VE.HANGVE =1 THEN 1 WHEN VE.HANGVE = 0 OR VE.HANGVE IS NULL THEN 0 END) SOVEHANG1, "
                            + " SUM (CASE WHEN VE.HANGVE =0 THEN 1 WHEN VE.HANGVE =1 OR VE.HANGVE IS NULL THEN 0 END) SOVEHANG2 "
                            + " FROM LICHBAY LB LEFT JOIN VE ON (LB.MALB = VE.MALB) "
                            + " WHERE (VE.LOAIVE =1 OR VE.LOAIVE IS NULL) AND YEAR(LB.NGAYDI) = " + nam.ToString() + " AND MONTH(LB.NGAYDI) = " + thang.ToString()
                            + " GROUP BY LB.MALB, LB.MACB) LB2 ON (LB2.MACB = CB.MACB) "
                            + " GROUP BY CB.MACB";

            DataTable bcthang_tab = SqlDataAccessHelper.ExecuteQuery(query);
            if (bcthang_tab == null)
                return null;
            List<BCThang_DTO> bcthang_list = new List<BCThang_DTO>();
            int tongDoanhThu = 0;
            for (int i = 0; i < bcthang_tab.Rows.Count; i++)
            {
                bcthang_list.Add(new BCThang_DTO());
                bcthang_list[i].maCB = bcthang_tab.Select()[i]["MaCB"].ToString();
                bcthang_list[i].soVe = int.Parse(bcthang_tab.Select()[i]["SoVe"].ToString());
                bcthang_list[i].doanhThu = int.Parse(bcthang_tab.Select()[i]["DoanhThu"].ToString());
                tongDoanhThu = tongDoanhThu + bcthang_list[i].doanhThu;
            }
            for (int i = 0; i < bcthang_list.Count; i++)
            {
                bcthang_list[i].tiLe = bcthang_list[i].doanhThu*100 / tongDoanhThu;
            }
            return bcthang_list;
        }
    }
}
